package com.excel.read;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import jxl.Cell;
import jxl.Hyperlink;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WriteException;




public class TableManagerImpl implements ExcelTableManager {




	
	public static int getRowNum(Sheet s, Cell aCell) {
		int row = s.getRows();
		int i = 0;
		for (i = aCell.getRow(); i < s.getRows(); i++) {
			Cell cell = s.getCell(2, i);
			String content = cell.getContents();
			if (content == null || content.trim().equals("")) {
				row = i - aCell.getRow();
				break;
			}
		}
		if (i == s.getRows()) {
			row = i - aCell.getRow();
		}
		return row;
	}

	public static int getColNum(Sheet s, Cell aCell) {
		int columns = s.getColumns();
		for (int j = 1; j < s.getColumns(); j++) {
			Cell cell = s.getCell(j, aCell.getRow());
			String content = cell.getContents();
			if (content == null || content.trim().equals("")) {
				columns = j;
				break;
			}

		}
		return columns;
	}


	public static List<Cell> findTables(Sheet s) {
		List<Cell> list = new ArrayList<Cell>();
		int curRowIndex = 1;
		int curColIndex = 0;
		int rows = s.getRows();
		int cols = s.getColumns();
		// ��������
		while (curRowIndex < s.getRows()) {
			Cell cell = s.getCell(2, curRowIndex);
			String content = cell.getContents();
			if (content != null && !content.trim().equals("")) {
				Cell aCell = s.getCell(2, curRowIndex - 1);
				content = aCell.getContents();
				if (content == null || content.trim().equals("")) {
					list.add(cell);
				}
			}
			curRowIndex++;
		}
		return list;
	}

	

	public AllTable readPmbTable(String addr) throws IOException,
			ClassNotFoundException {
		AllTable allTable = null;
		FileInputStream fileInputStream = null;
		try {
			fileInputStream = new FileInputStream(addr);
			ObjectInputStream objectInputStream = new ObjectInputStream(
					fileInputStream);
			allTable = (AllTable) (objectInputStream.readObject());
		} finally {
			if (fileInputStream != null) {
				fileInputStream.close();
			}
		}
		return allTable;
	}


	public void writePmbTable(String addr, AllTable allTable) throws IOException {
		FileOutputStream fileOutputStream = null;
		try {
			fileOutputStream = new FileOutputStream(addr);
			ObjectOutputStream objectOutputStream = new ObjectOutputStream(
					fileOutputStream);
			objectOutputStream.writeObject(allTable);
			objectOutputStream.flush();
			objectOutputStream.close();
		} finally {
			if (fileOutputStream != null) {
				fileOutputStream.close();
			}
		}
	}


	public void writeAllTable(String filePath, Sheet s) throws BiffException,
			IOException, SameNameException {
		
		AllTable allTable = Config.allTable;
		List<Cell> list = findTables(s);	
		Cell dateCell = s.getCell(0,0);
		
		String  aDate = dateCell.getContents();
		
		int maxPmbNo = (int)1;
		
		for (int m = 0; m < list.size(); m++) {
			Cell aCell = list.get(m);
			DBTable table = new DBTable();
			int rowNum = getRowNum(s, aCell);
			
			int colNum = getColNum(s, aCell);
			table.rowIndex = aCell.getRow();
			table.colIndex = aCell.getColumn() - 2;
			table.cols = colNum;
			table.rows = rowNum;
			aCell = s.getCell(table.colIndex, table.rowIndex - 1);
			
			table.name = aCell.getContents();
			table.pmbNo = maxPmbNo + m+1;
			table.data = new String[table.rows][table.cols];
			int i = 0;
			int j = 0;
			for (i = 0; i < table.rows; i++) {
				for (j = 0; j < table.cols; j++) {
					Cell cell = s.getCell(j + table.colIndex, i
							+ table.rowIndex);
					String content = cell.getContents();
					table.data[i][j] = content;
				}
			}
			Cell cell = s.getCell(table.colIndex, i	+ table.rowIndex);			
			String description = cell.getContents();
			table.setDescription(description);
			
			
			//取得列名在哪一列
			int codeIndex = -1;
			for(int k=0; k<table.data[0].length; k++) {
				if(table.data[0][k].equals("代码")) {
					codeIndex = k;
				}
			}
			table.codeIndex = codeIndex;
			
			
			//取得外键在哪一列
			int fkIndex = -1;
			for(int k=0; k<table.data[0].length; k++) {
				if(table.data[0][k].equals("外键")) {
					fkIndex = k;
				}
			}
			table.fkIndex = fkIndex;
			
			//取得类型在哪一列
			int typeIndex = -1;
			for(int k=0; k<table.data[0].length; k++) {
				if(table.data[0][k].equals("数据类型")) {
					typeIndex = k;
				}
			}
			table.typeIndex = typeIndex;
			//取得类型在哪一列
			int nullIndex = -1;
			for(int k=0; k<table.data[0].length; k++) {
				if(table.data[0][k].equals("允许为空")) {
					nullIndex = k;
				}
			}
			
			int pkIndex = -1;
			for(int k=0; k<table.data[0].length; k++) {
				if(table.data[0][k].equals("注释")) {
					pkIndex = k;
				}
			}
			
			//取得缺醒值在哪一列
			int defaultIndex = -1;
			for(int k=0; k<table.data[0].length; k++) {
				if(table.data[0][k].equals("缺省值")) {
					defaultIndex = k;
				}
			}
			
			//取得名称在哪一列
			int defaultDisplayIndex = -1;
			for(int k=0; k<table.data[0].length; k++) {
				if(table.data[0][k].trim().equals("名称")) {
					defaultDisplayIndex = k;
				}
			}
			
			//将第一列转为大写
			for (i = 1; i < table.rows; i++) {				
					table.data[i][0] = table.data[i][0].toUpperCase();
				
					//添加字段显示名字典
					String fieldName = table.name.toUpperCase()+"."+table.data[i][0].toUpperCase();
					allTable.addColumnName(fieldName, table.data[i][defaultDisplayIndex]);
					
			}
			
			table.defaultIndex = defaultIndex;
			table.nullIndex = nullIndex;
			table.fkIndex = fkIndex;
			table.codeIndex = codeIndex;
			table.pkIndex = pkIndex;
			
			allTable.put(table.name, table);						
			
		}
		
		
		


	}
	
	
	


	

	

	
	public List<ColumnCategory> getSameDate(String date, String type) {
		return null;
	}
	
	public AllTable getSameDatePmbTable(String date, String type) {
		List<ColumnCategory> list =  getSameDate(date, type);
		AllTable all = new AllTable();	
		List pmbNames = new ArrayList();
		for(int i=0; i<list.size(); i++) {
			ColumnCategory run = list.get(i);
			//��ǰ����
			String pmbName = run.getName();
			pmbNames.add(pmbName);
			DBTable pmbTable = all.get(pmbName);
			if(pmbTable == null) {
				pmbTable = new DBTable();
				pmbTable.name = pmbName;
				pmbTable.set.add(run);
				pmbTable.setPmbNo(run.getPmbNo().intValue());
				all.put(pmbName, pmbTable);				
			}else {
				pmbTable.set.add(run);
			}			
		}
		Iterator iterator = pmbNames.iterator();
		while(iterator.hasNext()) {
			String pmbName = (String)iterator.next();
			GridTable pmbTable = all.get(pmbName);
			pmbTable.tranform();
		}
		
		
		return all;
	}

	public AllTable getAllTable(String name) {
		// TODO Auto-generated method stub
		return null;
	}
	
	
	 
	
	 
	
	 
	
}
